<?php
// optin.php written by Ben Quartermaine June 2009
// Purpose: * confirms valid URL by checking email hash
//			* updates mysql enumerated status field to "in"

	$subscriberID = filter_input(INPUT_GET, "ID");
	$hashedEmail = filter_input(INPUT_GET, "Code");

	
	print "code: $hashedEmail <br />";
	print "ID: $subscriberID <br />";

	
	DBconfirmOptin($subscriberID, $hashedEmail);
	
	
	
    function DBconfirmOptin($subscriberID, $hashedEmail){
	 //connect to the database
	 $conn = mysql_connect("localhost", "root", "BlackBall56") 
	 or die(mysql_connect());
	 
	 //select databse
	 $select = mysql_select_db("emailSubs");
	 
	 //retrieve email from database
	 $sqlSelect = "SELECT email from subscribers where subscriberID = '$subscriberID';";
	 $selectResult = mysql_query($sqlSelect, $conn);
	 
	 $DBemailArray = mysql_fetch_assoc($selectResult);
	 $DBemail = $DBemailArray["email"];
	 
	 print "Email: $DBemail <br />";
	 
	 //verify email using md5hash
	 $localHash = hashEmail($DBemail);
	 
	 if ($localHash == $hashedEmail){
	 	//build and execute sql update statement
	 	$sqlUpdate = 
	 	"UPDATE subscribers 
	 	SET status = 'in'
		WHERE subscriberID = $subscriberID AND email = '$DBemail';";
	 
		print "$sql";

	 	$result = mysql_query($sqlUpdate, $conn);
	 
	 	//exception checking
	 	if($result){
	 		print "<h2>Your confirmation has been received</h2><br />";
		
			$affectedRows = mysql_affected_rows($conn);
		
			print "affected rows: $affectedRows";
		}else{
		 	print "<h2>There was a problem submitting your request. Please contact support</h2>";
  	 	}//end if
		
	 }
	 else
	 {
	 	print "this confirmation URL is invalid. <br />";
	 	
	 }//end if
	 
	  
	 //close database connection
	 mysql_close($conn);	 
	 
} //end insertSubscriberRecord()

function hashEmail($email){
	// create the MD5 hash 
  $secret_code = 'ffdidshawthe';
  $formatted_email = preg_replace("/(-|\@|\.)/", "", $email);
  $md5String = $secret_code . $formatted_email;
  $hashed = md5($md5String);
  
  return $hashed;
} //end hashEmail()
?>
